package com.epam.edu.dao;

import com.epam.edu.constant.Language;
import com.epam.edu.entity.OrdersMarks;
import com.epam.edu.entity.TeachingForm;
import com.epam.edu.entity.TeachingPlan;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

public class DAOTeachingForm {
    public static TeachingForm selectById(Connection connection, int id) {
        TeachingForm teachingForm = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String command = "SELECT id,teachingForm FROM TeachingForms WHERE id=?";
            statement = connection.prepareStatement(command);
            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                teachingForm = new TeachingForm(resultSet.getInt(1), resultSet.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return teachingForm;
    }

    public static List<TeachingForm> selectAll(Connection connection) {
        List<TeachingForm> teachingForms = new LinkedList<TeachingForm>();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String command = "SELECT id,teachingForm FROM TeachingForms";
            statement = connection.prepareStatement(command);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                TeachingForm teachingForm = new TeachingForm(resultSet.getInt(1), resultSet.getString(2));
                teachingForms.add(teachingForm);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return teachingForms;
    }

    public static TeachingForm selectByName(Connection connection, String form) {
        TeachingForm teachingForm = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String command = "SELECT id,teachingForm FROM TeachingForms WHERE teachingForm=?";
            statement = connection.prepareStatement(command);
            statement.setString(1, form);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                teachingForm = new TeachingForm(resultSet.getInt(1), resultSet.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return teachingForm;
    }

    public static List<TeachingForm> selectByMarks(Connection connection, OrdersMarks ordersMarks, Language language,
                                                   TeachingPlan teachingPlan) {
        List<TeachingForm> teachingForms = new LinkedList<TeachingForm>();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String command = "SELECT DISTINCT adm.tfId FROM Admissions adm " +
                    "INNER JOIN InputMarks im ON adm.specID=im.specId " +
                    "WHERE im.historyKZ<=? " +
                    "      AND im.language<=?" +
                    "      AND im.mathematics<=?" +
                    "      AND im.subjId=?" +
                    "      AND im.mark<=?" +
                    "      AND adm.eduLanguage=?" +
                    "      AND adm.tpId=?" +
                    "      AND adm.isDelete='false' AND im.isDelete='false'";
            statement = connection.prepareStatement(command);
            statement.setInt(1, ordersMarks.getHistoryKZMark());
            statement.setInt(2, ordersMarks.getLanguageMark());
            statement.setInt(3, ordersMarks.getMathematicsMark());
            statement.setInt(4, ordersMarks.getSubject().getId());
            statement.setInt(5, ordersMarks.getMark());
            statement.setString(6, language.toString());
            statement.setInt(7, teachingPlan.getId());
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                TeachingForm teachingForm = selectById(connection, resultSet.getInt(1));
                teachingForms.add(teachingForm);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return teachingForms;
    }
}
